package testPackage;

import java.io.*;
import java.sql.*;
import java.util.Properties;
import javax.servlet.*;
import javax.servlet.http.*;

public class DBTest extends HttpServlet {
	
	protected final String url = "jdbc:mysql://localhost:3306/todo";
	protected final String tableName = "tasks";
	protected final String username = "root";
	protected final String password ="admin";
	
	public void doGet(HttpServletRequest request, HttpServletResponse response) 
		throws ServletException, IOException {
				
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		String docType =
			"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +
			"Transitional//EN\"\n";
		String title = "Tasks";
		out.print(docType +
				"<html>\n" +
				"<head><title>" + title + "</title></head>\n" +
				"<link rel='stylesheet' href='./css/styles.css'\n" +
				"     type='text/css'>" +
				"<body>" +
				"<table class='title' border='5'>" +
				"  <tr><th>" + title + "</th></tr>" +
				"</table>" +
				"<p>");
		showTable(out);
		out.println("</body></html>");
	}
	
	protected void showTable(PrintWriter out) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = getConnection();
			Statement statement = connection.createStatement();
			String query = "select * from " + tableName;
			ResultSet resultSet = statement.executeQuery(query);
			printTableTop(connection, resultSet, out);
			printTableBody(resultSet, out);
			connection.close();
		} catch(Exception e) {
			System.err.println("Error: " + e);
		}
	}
		
	protected Connection getConnection() throws Exception {
		Properties userInfo = new Properties();
		userInfo.put("user", username);
		userInfo.put("password", password);
		
		Connection connection = DriverManager.getConnection(url, userInfo);
		return(connection);
	}
	
	protected void printTableTop(Connection connection, ResultSet resultSet,
									PrintWriter out) throws SQLException {
		out.println("<table border='1'>");
		String[] headingNames = { "ID", "Due Date", "Description", "Title" };
		out.print("<tr>");
		for (String headingName : headingNames) {
			out.printf("<th>%s", headingName);
		}
		out.println();
	}
	
	protected void printTableBody(ResultSet resultSet, PrintWriter out) 
		throws SQLException {
		
		while(resultSet.next()) {
			out.println("<tr align='right'>");
			out.printf("  <td>%d", resultSet.getInt("id"));
			out.printf("  <td>%d", resultSet.getDate("due_date"));
			out.printf("  <td>%s", resultSet.getString("description"));
			out.printf("  <td>%s", resultSet.getString("title"));
		}
		out.println("</table>");
	}
	
}
